//
//  DataBaseSingleng.m
//  UI_豆瓣
//
//  Created by dllo on 15/11/3.
//  Copyright © 2015年 蓝鸥科技. All rights reserved.
//

#import "DataBaseSingleng.h"
#import <sqlite3.h>
#import "ActivitesList.h"
#import "Movie.h"
#import "User.h"
@implementation DataBaseSingleng

+(instancetype)shareDataBase
{
    static DataBaseSingleng *dataBase = nil;
    if (dataBase == nil) {
        dataBase = [[DataBaseSingleng alloc]init];
    }
    return dataBase;
}

//声明数据库对象
static sqlite3 *db = nil;

#pragma mark - 打开数据库
-(void)openDB
{
    if (db != nil) {
        NSLog(@"数据库以打开");
        return;
    }
    NSString *sqlFile = [NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)lastObject];
    NSString *actPath = [sqlFile stringByAppendingPathComponent:@"DouBan.sqlite"];
    
    BOOL reslut = sqlite3_open(actPath.UTF8String, &db);
    
    if (reslut == SQLITE_OK) {
        NSLog(@"打开成功");
    }else{
        NSLog(@"打开失败");
    }
    NSLog(@"Path = %@",actPath);

}

#pragma mark -创建活动表

- (void)createActivityListTable{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS activityList(number INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT, begin_time TEXT, adderss TEXT, participant_count TEXT, wisher_count TEXT, category_name TEXT,end_time TEXT, image_hlarge TEXT, content TEXT, name TEXT)";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建表成功");
        
    }
}


#pragma mark -插入活动
- (void)insertActivityListSQL:(ActivitesList *)activity{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO activityList(title, begin_time, adderss, participant_count, wisher_count, category_name, end_time,image_hlarge, content, name) VALUES('%@', '%@', '%@', '%@', '%@', '%@','%@','%@','%@','%@')", activity.title, activity.begin_time, activity.address, activity.participant_count, activity.wisher_count, activity.category_name, activity.end_time,  activity.image_hlarge, activity.content, activity.name];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入成功");
        
        
    }
    
}

#pragma mark - 查讯活动
- (NSMutableArray *)selectActivity
{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM activityList"];
    
    //创建查询数据库跟随指针 (指针, 指向数据库上每一行数据)
    sqlite3_stmt *stmt = nil;
    
    //查询钱的准备工作
    //参数3 限制sql语句长度, -1为不限制 (将数据库对象db,sql语句, 跟随指针对象stmt关联到一起)
    BOOL result = sqlite3_prepare(db, sql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
    }
    else
    {
        NSLog(@"查询准备失败");
    }
    //循环遍历每一行数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        //取出第一列数据
        //        const unsigned char *ID = sqlite3_column_text(stmt, 0);
        
        
        const unsigned char *title = sqlite3_column_text(stmt, 1);
        const unsigned char *begin = sqlite3_column_text(stmt, 2);
        const unsigned char *address = sqlite3_column_text(stmt, 3);
        const unsigned char *par = sqlite3_column_text(stmt, 4);
        const unsigned char *wish = sqlite3_column_text(stmt, 5);
        const unsigned char *name = sqlite3_column_text(stmt, 10);
        const unsigned char *end = sqlite3_column_text(stmt, 7);
        const unsigned char *image_hlarge = sqlite3_column_text(stmt, 8);
        const unsigned char *con = sqlite3_column_text(stmt, 9);
        const unsigned char *cate = sqlite3_column_text(stmt, 6);

        
//        const unsigned char *image = sqlite3_column_text(stmt, 11);
        
        //将C语言类型转换成OC类型
        
        NSString *tit_ = [NSString stringWithUTF8String:(const char *)title];
        NSString *begin_ = [NSString stringWithUTF8String:(const char *)begin];
        
        NSString *address_ = [NSString stringWithUTF8String:(const char *)address];
        NSString *wish_ = [NSString stringWithUTF8String:(const char *)wish];
        NSString *par_ = [NSString stringWithUTF8String:(const char *)par];
        
        NSString *name_ = [NSString stringWithUTF8String:(const char *)name];
        NSString *end_ = [NSString stringWithUTF8String:(const char *)end];
        NSString *image_ = [NSString stringWithUTF8String:(const char *)image_hlarge];
        NSString *con_ = [NSString stringWithUTF8String:(const char *)con];
        NSString *cate_ = [NSString stringWithUTF8String:(const char *)cate];
        
        //        NSString *catalog_ = [NSString stringWithUTF8String:(const char *)catalog];
        //        NSString *user2 = [NSString stringWithUTF8String:(const char *)user];
        
        
        
        //创建活动对象
        ActivitesList *activity = [[ActivitesList alloc] init];
        activity.title = tit_;
        activity.begin_time = begin_;
        activity.end_time = end_;
        activity.address = address_;
        activity.wisher_count = wish_;
        activity.participant_count = par_;
        activity.category_name = cate_;
        
        activity.content = con_;
        activity.name = name_;
        activity.image_hlarge = image_;
        [array addObject:activity];
        
        
    }
    
    return array;
}



#pragma mark - 按标题名查讯活动
- (NSMutableArray *)selectActivity:(NSString *)title
{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM activityList WHERE title = '%@'", title];
    
    //创建查询数据库跟随指针 (指针, 指向数据库上每一行数据)
    sqlite3_stmt *stmt = nil;
    
    //查询钱的准备工作
    //参数3 限制sql语句长度, -1为不限制 (将数据库对象db,sql语句, 跟随指针对象stmt关联到一起)
    BOOL result = sqlite3_prepare(db, sql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
    }
    else
    {
        NSLog(@"查询准备失败");
    }
    //循环遍历每一行数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        //取出第一列数据
        //        const unsigned char *ID = sqlite3_column_text(stmt, 0);
        
        
        const unsigned char *title = sqlite3_column_text(stmt, 1);
        const unsigned char *begin = sqlite3_column_text(stmt, 2);
        const unsigned char *address = sqlite3_column_text(stmt, 3);
        const unsigned char *par = sqlite3_column_text(stmt, 4);
        const unsigned char *wish = sqlite3_column_text(stmt, 5);
        const unsigned char *name = sqlite3_column_text(stmt, 10);
        const unsigned char *end = sqlite3_column_text(stmt, 7);
        const unsigned char *image_hlarge = sqlite3_column_text(stmt, 8);
        const unsigned char *con = sqlite3_column_text(stmt, 9);
        const unsigned char *cate = sqlite3_column_text(stmt, 6);
        
        
        //        const unsigned char *image = sqlite3_column_text(stmt, 11);
        
        //将C语言类型转换成OC类型
        
        NSString *tit_ = [NSString stringWithUTF8String:(const char *)title];
        NSString *begin_ = [NSString stringWithUTF8String:(const char *)begin];
        
        NSString *address_ = [NSString stringWithUTF8String:(const char *)address];
        NSString *wish_ = [NSString stringWithUTF8String:(const char *)wish];
        NSString *par_ = [NSString stringWithUTF8String:(const char *)par];
        
        NSString *name_ = [NSString stringWithUTF8String:(const char *)name];
        NSString *end_ = [NSString stringWithUTF8String:(const char *)end];
        NSString *image_ = [NSString stringWithUTF8String:(const char *)image_hlarge];
        NSString *con_ = [NSString stringWithUTF8String:(const char *)con];
        NSString *cate_ = [NSString stringWithUTF8String:(const char *)cate];
        
        //        NSString *catalog_ = [NSString stringWithUTF8String:(const char *)catalog];
        //        NSString *user2 = [NSString stringWithUTF8String:(const char *)user];
        
        
        
        //创建活动对象
        ActivitesList *activity = [[ActivitesList alloc] init];
        activity.title = tit_;
        activity.begin_time = begin_;
        activity.end_time = end_;
        activity.address = address_;
        activity.wisher_count = wish_;
        activity.participant_count = par_;
        activity.category_name = cate_;
        
        activity.content = con_;
        activity.name = name_;
        activity.image_hlarge = image_;
        [array addObject:activity];
        
        
    }
    
    return array;
}





#pragma mark - 删除活动表
-(void)dropTable
{
    NSString *sql = @"DROP TABLE activityList";
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"删表成功");
    }else
    {
        NSLog(@"删表失败");
    }
    
}








#pragma mark - 创建电影表
- (void)createMovieTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS movie(movieId TEXT PRIMARY KEY , movieName TEXT, moviePic TEXT, user NSITEGER) ";
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"电影表创建成功");
    }
    else
    {
        NSLog(@"电影表创建失败");
    }
    
}

#pragma mark - 插入一个电影
- (void)insertMovie:(Movie *)movie
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO movie(movieId, movieName, moviePic) VALUES('%@', '%@', '%@')", movie.movieId, movie.movieName, movie.pic_url];
    
    int  result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"电影插入成功");
    }
    else
    {
        NSLog(@"电影插入失败  %d" ,result);
    }
}


#pragma mark - 删除电影表
- (void)deleteMovieTable
{
    NSString *sql = @"DROP TABLE movie";
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"电影表删除成功");
    }
    else
    {
        NSLog(@"电影表删除失败");
    }
    
}



#pragma mark - 查询电影
- (NSMutableArray *)selectMovie
{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM movie"];
    
    //创建查询数据库跟随指针 (指针, 指向数据库上每一行数据)
    sqlite3_stmt *stmt = nil;
    
    //查询钱的准备工作
    //参数3 限制sql语句长度, -1为不限制 (将数据库对象db,sql语句, 跟随指针对象stmt关联到一起)
    BOOL result = sqlite3_prepare(db, sql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        NSLog(@"查询准备成功");
    }
    else
    {
        NSLog(@"查询准备失败");
    }
    //循环遍历每一行数据
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        //取出第一列数据
        const unsigned char *ID = sqlite3_column_text(stmt, 0);
        
        //取出第二列数据
        const unsigned char *name = sqlite3_column_text(stmt, 1);
        
        //取出第三列数据
        const unsigned char *pic = sqlite3_column_text(stmt, 2);
        //        //取出第四列数据
        //        const unsigned char *user = sqlite3_column_text(stmt, 3);
        //        NSLog(@"id == %s", ID);
        
        //将C语言类型转换成OC类型
        NSString *Id = [NSString stringWithUTF8String:(const char *)ID];
        NSString *name2 = [NSString stringWithUTF8String:(const char *)name];
        NSString *pic2 = [NSString stringWithUTF8String:(const char *)pic];
        //        NSString *user2 = [NSString stringWithUTF8String:(const char *)user];
        
        
        
        //创建电影对象
        Movie *movie = [[Movie alloc] init];
        movie.movieName = name2;
        movie.movieId = Id;
        movie.pic_url = pic2;
        [array addObject:movie];
        
    }
    return array;
    
}

#pragma mark -- 创建用户表
-(void)createUserTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS user(userName TEXT INTEGER PRIMARY KEY, passworld TEXT)";
    
    BOOL result = sqlite3_exec(db, sql.UTF8String,NULL , NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"创建User表成功");
    }else{
        NSLog(@"创建User表失败");
    }
}


#pragma mark -- 插入用户
-(void)insertUser:(User *)user
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO user(userName, passworld) VALUES('%@', '%@')",user.userName, user.passworld];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"插入用户成功");
    }else{
        NSLog(@"插入用户失败");
    }
}

#pragma mark -- 查询用户
-(NSMutableArray *)selectUser
{
    NSMutableArray *array = [NSMutableArray array];
    NSString *sql = @"SELECT * FROM user";
    
    sqlite3_stmt *stmt = nil;
    
    BOOL result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"查询用户准备成功");
    }else{
        NSLog(@"查询用户准备失败");
    }
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        const unsigned char *userName = sqlite3_column_text(stmt, 0);
        const unsigned char *passworld = sqlite3_column_text(stmt, 1);
        
        
        NSString *nameStr = [NSString stringWithUTF8String:(const char *)userName];
        NSString *passStr = [NSString stringWithUTF8String:(const char *)passworld];
        
        User *user = [[User alloc]init];
        user.userName = nameStr;
        user.passworld = passStr;
        [array addObject:user];
    }
    return array;
}


#pragma mark -- 创建我的活动表
-(void)createMyActivityTable
{
    NSString *sql = @"CREATE TABLE IF NOT EXISTS MyActivity(numeber INTEGER PRIMARY KEY AUTOINCREMENT, activityTitle TEXT, userName TEXT)";
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"创建我的活动表成功");
    }
    else
    {
        NSLog(@"创建我的活动表失败");
    }

}


#pragma mark -- 收藏活动
-(void)insertMyActivity:(NSString *)title :(NSString *)name
{
    NSString *sql = [NSString stringWithFormat:@"INSERT INTO MyActivity( activityTitle, userName) VALUES('%@', '%@')", title ,name];
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    
    if (result == SQLITE_OK) {
        
        NSLog(@"收藏成功");
    }
    else
    {
        NSLog(@"收藏失败");
    }

}


#pragma mark - 删除活动收藏表
- (void)deleteMyActivityTable:(NSString *)title name:(NSString *)name
{
    NSString *sql =[NSString stringWithFormat: @"DELETE FROM  MyActivity WHERE activityTitle = '%@' and userName = '%@'", title, name] ;
    
    BOOL result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, NULL);
    if (result == SQLITE_OK) {
        NSLog(@"我的活动删除成功");
    }
    else
    {
        NSLog(@"我的活动删除失败");
    }
    
}



#pragma mark -- 查询我的活动
-(NSMutableArray *)selectMyActvity:(NSString *)userName
{
    NSMutableArray *array = [NSMutableArray array];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM myActivity WHERE userName = '%@'", userName];
    
    NSLog(@"______%@",userName);
    
    sqlite3_stmt *stmt = nil;
    
    BOOL result = sqlite3_prepare(db, sql.UTF8String, -1, &stmt, NULL);
    
    if (result == SQLITE_OK) {
        NSLog(@"查询收藏准备成功");
    }else{
        NSLog(@"查询收藏准备失败");
    }
    
    while (sqlite3_step(stmt) == SQLITE_ROW) {
        
        const unsigned char *title = sqlite3_column_text(stmt, 1);
//        const unsigned char *userName = sqlite3_column_text(stmt, 2);
       
        
        NSString *title1 = [NSString stringWithUTF8String:(const char *) title];
//        NSString *user = [NSString stringWithUTF8String:(const char *) userName];
         NSLog(@"=========%@",title1);
        
    
        [array addObject:title1];
        
       
    }
     NSLog(@"2222222%@",array);
    return array;
}




@end
